ajDBGetLOB function

Available since AlchemyJ v4.1

Description

The ajDBGetLOB function retrieves a file from a Large Object column in a database table satisfying the criteria specified in filter_condition. Please take note that to run this function from Excel, you need to set up the Data Source Connection in ##ExternalResources and config the table in DB Schema.

Syntax

ajDBGetLOB( table_name, lob_info, filter_type, filter_condition, table_schema, [data_source_id], [run_condition], [run_by_function_point_only] )

Argument Name	Argument Type	Description
table_name (required)	String	The name of the table that stores the file.
lob_info (required)	Range / Array	The range that contains the LOB field information. Refer to the LOB Info snippet to see how you can use this snippet to define the required fields.
filter_type (required)	Double	The type of filter. The value can be 0, 1 or 2.
filter_condition (required)	Range / Array	The range that defines the filter condition. filter_type = 0. The range defines the WHERE clause of a SQL statement. The range will be concatenated into a single string. For example, Name = 'peter' and class = 'B'. filter_type = 1. The range defines the filter condition in the Kendo grid style. Click Insert Snippet\Filter Condition (Filter Type 1) to add the preset required format. filter_type =2. The range defines a filter condition similar to the format used in MS Query. Click Insert Snippet\Filter Condition (Filter Type 2) to add the preset required format. Refer to the Filter Condition snippet to see how you can use this snippet to define the required fields for filter type 1, 2.
table_schema (required)	Range / Array	Specifies the Excel range that defines the DB Schema. The key column(s) must be included in the Column_headers and Data. Otherwise, an error will be raised.
data_source_id (optional)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon / Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Number of affected records.

2) Return Type: Single Value / Multiple values (array formula).

Example

Before using the function, you need to set up the Data Source Connection in ##ExternalResources worksheet and config the table in DBSchema worksheet.

Before running this function point, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.

We will use the following table. The table name is tb_supporting_doc. It has 6 columns and 2 rows, how to save a file to database,refer to ajDBUpdateLOB .

Example 1 - Filter Type is 0

Download a image from database, image is saved in FILE_CONTENT column ,the condition is FILE_ID=1001. 1 record is affected.

The LOB Info table was created via AlchemyJ ribbon Insert Snippet and select LOB Info , make sure there are 5 empty rows, otherwise inserting Filter Condition will fail.

Click Add Component to and select DB Schema to add the DB Schema and input the table name.

The picture is downloaded to D:\download folder, if the folder path does not exist, it will be created automatically.

Example 2 - Filter Type is 1

Download a file from database, file is saved in FILE_CONTENT column,the filter condition is IS_DELETE <1 and IS_DELETE >=0 and filter_type is 1.

The filter condition table was created via AlchemyJ ribbon Insert Snippet and select Filter Condition (Filter Type 1) , make sure there are 5 empty rows, otherwise inserting Filter Condition will fail.

The values of parameters are as below. More details about Filter Condition, refer to Filter Condition

Since the LOB File Name is provided (DBLOB.txt), the name of the download file is DBLOB.txt.

Click here to download the use case workbooks for further reference.

Notes

1. The column that stores the file should be a LOB (Large Object) or a CLOB (Character Large Object).
2. Only one file can be stored in a LOB field.
3. If the filter condition does not match anything, the function will return 0.
4. It is expected that the filter condition should match one record only. If more than one record is found, the file in the last record will be saved.
5. If the file exists, it will be overwritten.
6. For more details about filter options, you can refer to the ajDBReadRecord function.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
DB connection error.
Table name is empty.
Filter type is invalid, it must be 0, 1, or 2.
Data name in filter condition does not exist in the provided table schema.
Total number of filter condition columns is not even number.
Data name in filter condition is empty.
Operator in filter condition is empty.
Invalid operator value in filter condition.
And/Or in filter condition is invalid.
Content in filter condition range does not meet the expected format of specified filter type.
Either filter condition or table schema must be provided.
Table name does not exist in provided DB Schema.
The table schema columns are invalid.
The table schema range does not include a header or a row of data.
The provided column header cannot be found in the specified table schema range.
No Key order value defined in DB Schema.
Column header is not a single row range or a single column range.
Invalid filter condition, it can only be 0, 1 or 2.
LOB info is empty.
LOB content field in LOB info is empty.
LOB content folder in LOB info is empty.
LOB file name in LOB info is empty.
LOB content folder in lob info not found.
LOB info range is invalid.
LOB field name cannot be found in the database.
LOB character set field cannot be found in the database.
Failed to create LOB Content Folder.
Upload file is not found.
CLOB defined in DB Schema but CLOB character set field in LOB info is empty.
CLOB defined in DB Schema but CLOB character set in LOB info is empty.